﻿// ******************************************************************
// * Copyright(c)  Steven Lu
// * Description   : Entity_PROD_PLAN_ORDER
// * CreateDate    : 2011/8/16 10:16:40
// * Creater       : CodeSmith
// * LastChangeDate: 2011/8/16 10:16:40
// * LastChanger   : 2011/8/16 10:16:40
// * Version Info  : 1.0 For ESOL
// ******************************************************************

using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Suzsoft.Smart.Data;
using ESOL.Common.Entity;
using ESOL.Common;

namespace ESOL.Business.Prod
{
    /// <summary>
    /// ProdPlanOrder
    /// </summary>
    public partial class ProdPlanOrderBs
    {
        #region GetByID
        /// <summary>
        /// Get Entity
        /// </summary>
        /// <param name="objProdPlanOrderEntity">Entity</param>
        /// <returns>Entity</returns>
        public static ProdPlanOrderEntity GetProdPlanOrder(ProdPlanOrderEntity objProdPlanOrderEntity)
        {
            return DataAccess.SelectSingle<ProdPlanOrderEntity>(objProdPlanOrderEntity);
        }

        #endregion

        #region Insert
        /// <summary>
        /// Insert Data
        /// </summary>
        /// <param name="objProdPlanOrderEntity">Entity</param>
        /// <returns>true Insert Succeed; false Insert Fail</returns>
        public static bool InsertProdPlanOrder(ProdPlanOrderEntity objProdPlanOrderEntity)
        {
            return DataAccess.Insert(objProdPlanOrderEntity);
        }

        public static bool InsertProdPlanOrder(List<ProdPlanOrderEntity> objProdPlanOrderEntityList)
        {
            return DataAccess.Insert(objProdPlanOrderEntityList);
        }
        #endregion

        #region Update
        /// <summary>
        /// Update Data
        /// </summary>
        /// <param name="objProdPlanOrderEntity">Entity</param>
        /// <returns>true Update Succeed; false Update Fail</returns>
        public static bool UpdateProdPlanOrder(ProdPlanOrderEntity objProdPlanOrderEntity)
        {
            return DataAccess.Update(objProdPlanOrderEntity);
        }

        public static bool UpdateProdPlanOrder(List<ProdPlanOrderEntity> objProdPlanOrderEntityList)
        {
            return DataAccess.Update<ProdPlanOrderEntity>(objProdPlanOrderEntityList);
        }
        #endregion

        #region Delete
        /// <summary>
        /// Delete Data
        /// </summary>
        /// <param name="objProdPlanOrderEntity">Entity</param>
        /// <returns>true Delete Succeed; false Delete Fail</returns>
        public static bool DeleteProdPlanOrder(ProdPlanOrderEntity objProdPlanOrderEntity)
        {
            return DataAccess.Delete(objProdPlanOrderEntity);
        }

        public static bool DeleteProdPlanOrder(List<ProdPlanOrderEntity> objProdPlanOrderEntityList)
        {
            return DataAccess.Delete<ProdPlanOrderEntity>(objProdPlanOrderEntityList);
        }
        #endregion

        #region GetAll
        /// <summary>
        /// Get All Data
        /// </summary>
        /// <returns>Back All Data<returns>
        public static DataSet GetProdPlanOrderAll()
        {
            return DataAccess.SelectAllDataSet<ProdPlanOrderEntity>();
        }
        /// <summary>
        /// Get All Data List
        /// </summary>
        /// <returns>Back All Data List<returns>
        public static List<ProdPlanOrderEntity> GetProdPlanOrderAllList()
        {
            return DataAccess.SelectAll<ProdPlanOrderEntity>();
        }
        #endregion

        public static DataSet GetAllDsByCondition(int pageIndex, int pageCount, string condition)
        {
            string sql = "";
            if (string.IsNullOrEmpty(condition))
            {
                string sqlSource = @"select top {0} *,(select count(1) from PROD_PLAN_ORDER) as cnt
from PROD_PLAN_ORDER
where id not in (
    select top ({1}*({2}-1)) id from PROD_PLAN_ORDER order by created_date desc
) order by created_date desc";
                sqlSource = string.Format(sqlSource, pageCount, pageCount, pageIndex);
                sql = @"select po.*,app.ID as SALES_APP_ID,
app.APP_NO as SALES_APP_NO,u1.firstName as CREATED_USER,u2.firstName as UPDATED_USER  
from ({0}) po
left join SALES_APP_ITEM appi on po.SAPP_ITEM_ID=appi.ID
left join SALES_APP app on appi.APP_ID=app.ID
left join BPMDB.dbo.PW_User u1 on po.CREATED_BY=u1.userNo
left join BPMDB.dbo.PW_User u2 on po.UPDATED_BY=u2.userNo
order by po.created_date desc";
                sql = string.Format(sql, sqlSource);
            }
            else
            {
                sql = @"select top {0} *,(select count(1) from ({3}) t) as cnt
from ({3}) t
where t.ID not in (
    select top ({1}*({2}-1)) ID from ({3}) t order by created_date desc
) order by created_date desc";
                string sqlSource = @"select po.*,app.ID as SALES_APP_ID,
app.APP_NO as SALES_APP_NO,u1.firstName as CREATED_USER,u2.firstName as UPDATED_USER   
from PROD_PLAN_ORDER po
left join SALES_APP_ITEM appi on po.SAPP_ITEM_ID=appi.ID
left join SALES_APP app on appi.APP_ID=app.ID
left join BPMDB.dbo.PW_User u1 on po.CREATED_BY=u1.userNo
left join BPMDB.dbo.PW_User u2 on po.UPDATED_BY=u2.userNo
where 1=1 ";
                sqlSource += condition;
                sql = string.Format(sql, pageCount, pageCount, pageIndex, sqlSource);
            }
            return DataAccess.SelectDataSet(sql);
        }

       

        public static bool PoToWo(ProdPlanOrderEntity po, List<ProdWoEntity> wos, List<ProdWoTaskEntity> wts)
        {
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    broker.BeginTransaction();
                    DataAccess.Update(po, broker);
                    DataAccess.Insert(wos, broker);
                    DataAccess.Insert(wts, broker);
                    broker.Commit();
                    return true;
                }
                catch (Exception ex)
                {
                    broker.RollBack();
                    throw ex;
                }
            }
        }


        /// <summary>
        /// return null 表示没有生单成功
        /// </summary>
        /// <param name="userNo"></param>
        /// <param name="mpo"></param>
        /// <returns></returns>
        public static List<ProdWoEntity> CreateWo(string userNo, ProdMaintainPo mpo)
        {

            if (mpo == null) return null;
            ProdPlanOrderEntity po = ProdPlanOrderBs.GetProdPlanOrder(new ProdPlanOrderEntity { Id = mpo.Id });

            DataSet dsBomItems = ESOL.Business.Bpt.BomItemBs.GetBomItemByMasterId(mpo.BomId);

            List<ProdWoEntity> wos = new List<ProdWoEntity>();
            foreach (DataRow bomItem in dsBomItems.Tables[0].Rows)
            {
                if (bomItem["ITEM_TYPE"].ToString().Equals("PDTYPE01") || bomItem["ITEM_TYPE"].ToString().Equals("PDTYPE04"))
                {
                    //产品默认的工艺路线
                    var bomTechnic = ESOL.Business.Bpt.BomTechnicBs.GetBomTechnic(new BomTechnicEntity { ProductId = bomItem["MTRL_ID"].ToString(), IsActive = 1, Status = Common.WfStatus.WFS30.ToString() });

                    //工单
                    ProdWoEntity wo = new ProdWoEntity();
                    wo.Id = Guid.NewGuid().ToString();
                    wo.IsActive = 1;
                    wo.CreatedBy = userNo;
                    wo.CreatedDate = DateTime.Now;
                    wo.UpdatedBy = userNo;
                    wo.UpdatedDate = DateTime.Now;
                    wo.Status = Common.WoStatus.WOS_Daft.ToString();
                    string woNo = ESOL.Business.Mst.MstCodeRuleBs.GetCurrentOrderCode("WorkOrder");
                    wo.WoNo = woNo;
                    wo.PoId = po.Id;
                    wo.BomItemId = bomItem["ID"].ToString();
                    wo.BomTechnicId =(bomTechnic==null)?"": bomTechnic.Id;
                    wo.ProductId = bomItem["Mtrl_ID"].ToString();
                    wo.ProductCode = bomItem["PRODUCT_CODE"].ToString();
                    wo.ProductName = bomItem["PRODUCT_NAME"].ToString();
                    if (wo.ProductId.Equals(mpo.ProductId))
                    {
                        wo.PlanStartDate = mpo.StartTime;
                        wo.ReqDate = mpo.RequiredDate;
                    }
                    if(!string.IsNullOrEmpty(bomItem["PARENT_ID"].ToString()))
                    {
                        foreach (DataRow mdr in dsBomItems.Tables[0].Rows)
                            if (mdr["ID"].ToString().Equals(bomItem["PARENT_ID"].ToString()))
                            {
                                wo.ParentWoNo = mdr["MTRL_ID"].ToString();
                                break;
                            }
                    }
                        
                    wo.Qty = po.Qty * (Convert.ToDecimal(bomItem["UNIT_DOSE"]));
                    
                    //用于计算可用量
                    wo.StorageQty = 0;
                    wos.Add(wo);
                }
            }

            //构建工单的父子关系
            foreach (ProdWoEntity wo in wos)
            {
                if (!string.IsNullOrEmpty(wo.ParentWoNo))
                {
                   ProdWoEntity prod= wos.Find(delegate(ProdWoEntity p) { return p.ProductId == wo.ParentWoNo; });
                   if (prod == null) return null;
                   wo.ParentWoNo = prod.Id;
                }
            }
            return wos; 
        }


        public static List<ProdPlanOrderEntity> GetUpdateScheduleRelationPoList(string poId)
        {
            List<ProdPlanOrderEntity> plans=new List<ProdPlanOrderEntity>();
            ProdPlanOrderEntity planEntity = DataAccess.SelectSingle(new ProdPlanOrderEntity { Id = poId });
            string strSql = "Select * from  PROD_PLAN_ORDER where ID <>'" + poId + "' AND ( (DELIVERY_DATE>'" + planEntity.StartTime + "' and DELIVERY_DATE<='" + planEntity.DeliveryDate + "') or(START_TIME>='" + planEntity.StartTime + "' and START_TIME<'" + planEntity.DeliveryDate + "'))";

           DataSet ds= DataAccess.SelectDataSet(strSql);
           plans.Add(planEntity);
           if (ds != null && ds.Tables.Count > 0)
           {
               foreach (DataRow dr in ds.Tables[0].Rows)
               {
                   plans.Add(new ProdPlanOrderEntity(dr));
               }
           }
           return plans;
        }


        public static bool InsertProdPlanOrderWithSoNo(ProdPlanOrderEntity objProdPlanOrderEntity)
        {
             List< SalesSoItemEntity> DeleteSoItem = DataAccess.Select<SalesSoItemEntity>(new SalesSoItemEntity { Remark = objProdPlanOrderEntity.PoNo });
             foreach (SalesSoItemEntity item in DeleteSoItem)
             {
                 item.Remark = "";
             }
            List<SalesSoItemEntity>UpdateSoItem=new List<SalesSoItemEntity>();
            if (!string.IsNullOrEmpty(objProdPlanOrderEntity.SappItemId))
            {
                foreach (string soNo in objProdPlanOrderEntity.SappItemId.Split(';'))
                {
                    SalesSoItemEntity item = DataAccess.SelectSingle<SalesSoItemEntity>(new SalesSoItemEntity { SoNo = soNo, MaterialCode = objProdPlanOrderEntity.ProductCode });
                    if (item != null)
                    {
                        item.Remark = objProdPlanOrderEntity.PoNo;
                        UpdateSoItem.Add(item); 
                    }
                }
            }

            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    broker.BeginTransaction();
                    if (DeleteSoItem != null && DeleteSoItem.Count > 0) DataAccess.Update(DeleteSoItem, broker);
                    if (UpdateSoItem != null && UpdateSoItem.Count > 0) DataAccess.Update(UpdateSoItem, broker);
                    DataAccess.Insert(objProdPlanOrderEntity);
                    broker.Commit();
                    return true;
                }
                catch (Exception ex)
                {
                    broker.RollBack();
                    throw ex;
                }
            }

           
        }

        public static bool UpdateProdPlanOrderWithSoNo(ProdPlanOrderEntity objProdPlanOrderEntity)
        {
            List<SalesSoItemEntity> DeleteSoItem = DataAccess.Select<SalesSoItemEntity>(new SalesSoItemEntity { Remark = objProdPlanOrderEntity.PoNo });
            foreach (SalesSoItemEntity item in DeleteSoItem)
            {
                item.Remark = "";
            }
            List<SalesSoItemEntity> UpdateSoItem = new List<SalesSoItemEntity>();
            if (!string.IsNullOrEmpty(objProdPlanOrderEntity.SappItemId))
            {
                foreach (string soNo in objProdPlanOrderEntity.SappItemId.Split(';'))
                {
                    SalesSoItemEntity item = DataAccess.SelectSingle<SalesSoItemEntity>(new SalesSoItemEntity { SoNo = soNo, MaterialCode = objProdPlanOrderEntity.ProductCode });
                    if (item != null)
                    {
                        item.Remark = objProdPlanOrderEntity.PoNo;
                        UpdateSoItem.Add(item);
                    }
                }
            }

            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    broker.BeginTransaction();
                    if (DeleteSoItem != null && DeleteSoItem.Count > 0) DataAccess.Update(DeleteSoItem, broker);
                    if (UpdateSoItem != null && UpdateSoItem.Count > 0) DataAccess.Update(UpdateSoItem, broker);
                    DataAccess.Update(objProdPlanOrderEntity);
                    broker.Commit();
                    return true;
                }
                catch (Exception ex)
                {
                    broker.RollBack();
                    throw ex;
                }
            }


        }



        public static DataSet GetProductionReqQty(string poNo)
        {
            string sql = "select * from dbo.SALES_SO_ITEM where len(isnull(remark,''))=0 ";
            if (!string.IsNullOrEmpty(poNo))
                sql += "  or remark='" + poNo + "'";
            DataSet ds = DataAccess.SelectDataSet(sql);
            return ds;
        }


        public static DataSet GetProductionSalesBalanceQuery()
        {
            string sql = @"select ITEM.SO_NO ,ITEM.MATERIAL_CODE,ITEM.MATERIAL_NAME,ITEM.QTY, MATERIAL_SPEC ,MATERIAL,UNIT,DELIVERY_DATE,
(SELECT ISNULL(SUM(CURRENT_QTY),0) FROM INV_STG STG WHERE STG.PRODUCT_ID=ITEM.MATERIAL_ID ) AS STG_QTY,
(SELECT ISNULL(SUM(SHIPED_QTY),0) FROM dbo.SALES_SHIP S LEFT JOIN dbo.SALES_SHIP_ITEM Si ON S.ID=SI.SHIP_ID
WHERE SI.MATERIAL_CODE=ITEM.MATERIAL_CODE AND ITEM.SO_ID= S.SOB_ID AND S.STATUS='WFS30') AS SHIPED_QTY
,PROD.SCH_QTY
FROM SALES_SO_ITEM ITEM
LEFT JOIN 
(select P.PRODUCT_CODE, SUM( CASE WHEN ISNULL(WO.QTY,0)>0 THEN WO.QTY-ISNULL(STORAGE_QTY,0) ELSE P.QTY END ) SCH_QTY 
from dbo.PROD_PLAN_ORDER P LEFT JOIN PROD_WO WO ON P.ID=WO.PO_ID AND P.PRODUCT_CODE=WO.PRODUCT_CODE
WHERE P.STATUS IN('PSTATUS01','PSTATUS06','PSTATUS08')
GROUP BY P.PRODUCT_CODE)
PROD ON ITEM.MATERIAL_CODE=PROD.PRODUCT_CODE
LEFT JOIN SALES_SO SO ON ITEM.SO_NO=SO.SO_NO
WHERE SO.STATUS IN ('WFS10','WFS20','WFS30')";
            
            DataSet ds = DataAccess.SelectDataSet(sql);
            return ds;
        }

        
        public static DataTable GetPOMtrlReq()
        {
            string sql = @"select A.PO_NO,A.PRODUCT_ID,A.PRODUCT_CODE,A.PRODUCT_NAME,A.QTY ,A.DELIVERY_DATE,A.STATUS
,B.MTRL_ID,B.UNIT_DOSE,B.LOSS_RATE,B.UNIT,C.PRODUCT_CODE AS MTRL_NO,C.PRODUCT_NAME AS MTRL_NAME ,(A.QTY*B.UNIT_DOSE) AS PP_REQ_QTY
, ISNULL(h.HOLD_QTY,0) as HOLD_QTY,isnull(stg.STG_QTY,0) STG_QTY
, ISNULL(TRANS.TRANS_QTY,0) TRANS_QTY,(A.QTY*B.UNIT_DOSE-isnull(TRANS_QTY,0)) REQ_QTY
 from prod_plan_order a left join bom_item b on a.bom_id=b.bom_master_id
 LEFT JOIN MST_PRODUCT C ON B.MTRL_ID=C.PRODUCT_ID
 left join 
 (SELECT SUM(QTY) HOLD_QTY,MTRL_CODE FROM  [PROD_PP_MTRL_HOLD] GROUP BY MTRL_CODE ) h on h.mtrl_CODE=c.product_code
 left join ( select sum(current_qty) STG_QTY ,S.PRODUCT_ID
  from dbo.INV_STG S 
  GROUP BY S.PRODUCT_ID ) stg  on stg.product_id=b.mtrl_id
 LEFT JOIN 
  (SELECT SUM(TRANS_QTY) AS TRANS_QTY,I.MATERIAL_ID MTRL_ID,I.MATERIAL_NAME MTRL_NAME,I.MATERIAL_CODE MTRL_NO,P.PO_NO FROM dbo.INV_MTRL_TRANS T LEFT JOIN PROD_WO W ON T.WO_NO=W.WO_NO
 LEFT JOIN dbo.INV_MTRL_TRANS_ITEM I ON I.TRANS_NO=T.TRANS_NO
 LEFT JOIN prod_plan_order P ON W.PO_ID=P.ID
 WHERE P.status='PSTATUS06' and ISNULL(P.IS_MTRL_HOLD,0)=0 AND T.TRANS_TYPE='Mtrl_Req'
 GROUP BY I.MATERIAL_ID ,I.MATERIAL_NAME ,I.MATERIAL_CODE ,P.PO_NO) TRANS
 ON A.PO_NO=TRANS.PO_NO AND TRANS.MTRL_NO=c.product_code
 where a.status='PSTATUS06' and ISNULL(a.IS_MTRL_HOLD,0)=0  and (b.item_type='PDTYPE02' or b.item_type='PDTYPE05')
 ";

            DataSet ds = DataAccess.SelectDataSet(sql);
            if (ds != null && ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;

        }
        
        public static DataTable GetMtrlHoldByPo()
        {
            string sql = "select  SUM(QTY) QTY,MTRL_CODE,MTRL_ID,MTRL_NAME from dbo.PROD_PP_MTRL_HOLD where qty>0 GROUP BY MTRL_CODE,MTRL_ID,MTRL_NAME ";

            DataSet ds = DataAccess.SelectDataSet(sql);
            if (ds != null && ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;

        }
                
    }

}

